﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Agile.Service {
    public class DataBaseTransportDAL {
        private static SqlConnection conn;
        private static SqlCommand cmd;
        private static SqlDataAdapter adapter;
        public static string GetConnString(string server,string username,string password,string database) {
            string connStr = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}",server,database,username,password);
            return connStr;
        }
        /// <summary>
        /// 取消 Command 执行，并关闭 DataReader 对象和数据连接
        /// </summary>
        public void Dispose() {
            if(cmd != null) {
                cmd.Cancel();
                cmd.Dispose();
            }
            if(conn.State != ConnectionState.Closed) {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 查询并返回一个数据表
        /// </summary>
        /// <param name="cmdTxt">SQL操作语句</param>
        /// <param name="connstr">连接字符串</param>
        /// <returns></returns>
        public DataTable GetFillData(string cmdTxt,string connstr) {
            conn = new SqlConnection(connstr);
            conn.Open();
            DataSet ds = new DataSet();
            try {
                using(cmd = new SqlCommand(cmdTxt,conn)) {
                    using(adapter = new SqlDataAdapter(cmd)) {
                        adapter.Fill(ds);
                        Dispose();
                        return ds.Tables[0];
                    }
                }
            } catch(Exception ex) {
                Logger.LogError(this.GetType().Name+"->GetFillData",ex);
                Dispose();
                return null;
            } finally {
                Dispose();
            }
        }
        public int GetExcuteNonQuery(string cmdTxt,string connstr) {
            conn = new SqlConnection(connstr);
            conn.Open();
            using(cmd = new SqlCommand(cmdTxt,conn)) {
                try {
                    cmd.CommandTimeout = 180;
                    int result = cmd.ExecuteNonQuery();
                    Dispose();
                    return result;
                } catch(Exception ex) {
                    Logger.LogError(this.GetType().Name + "->GetExcuteNonQuery",ex);
                    Dispose();
                    return 0;
                } finally {
                    Dispose();
                }
            }
        }

        public DataTable GetDataBase(string connstr) {
            string cmdTxt = "select name as text,name as id From master.dbo.sysdatabases order by name ";
            DataTable data = GetFillData(cmdTxt,connstr);
            return data;
        }
        public DataTable GetTableList(string connstr) {
            string cmdTxt = "SELECT Name as text,Name as id FROM SysObjects Where XType='U' ORDER BY Name";
            DataTable data = GetFillData(cmdTxt,connstr);
            return data;
        }
        public DataTable GetTerm(string connstr,string table) {
            string cmdTxt = "SELECT NAME as text,name as id FROM SYSCOLUMNS where id=Object_id('" + table + "') ";
            DataTable data = GetFillData(cmdTxt,connstr);
            return data;
        }

    }
}
